----had to update the data because did not clean 6 records properly in excel file
--UPDATE [70-461].[dbo].[superbowl] 
--SET Season = 1966
--WHERE counter = 55;


--selects all qbs who have been to a superbowl
SELECT a.Season as SuperbowlsPlayed, b.No#, b.College as College, b.Name as Name FROM [70-461].[dbo].[superbowl] as a
LEFT JOIN [70-461].[dbo].[DRAFT] as b
ON a.WinningQb = b.Name OR  a.LosingQB = b.Name

;
--selects all qbs who have been to a superbowl and were 1st round picks
SELECT MAX(a.Season) as SuperbowlsPlayed, MAX(b.Round) as DraftRoundPick, MAX(b.College) as College, b.Name as Name FROM [70-461].[dbo].[superbowl] as a
LEFT JOIN [70-461].[dbo].[DRAFT] as b
ON a.WinningQb = b.Name OR  a.LosingQB = b.Name
WHERE b.Round = 1
GROUP BY b.Name
ORDER BY 1 DESC;

--SELECT * FROM [70-461].[dbo].[superbowl] 


SELECT * FROM [70-461].[dbo].[superbowl] 
SELECT * FROM [70-461].[dbo].[Draft] 

--Selects every quarterback who has played in a superbowl and the amount of times they have went to the superbowl
SELECT count(a.Season) as SuperbowlsPlayed, b.Name as Name FROM [70-461].[dbo].[superbowl] as a
INNER JOIN [70-461].[dbo].[DRAFT] as b
ON a.WinningQb = b.Name OR  a.LosingQB = b.Name
GROUP BY b.Name
ORDER BY 1 DESC
;

--joins the table containing every qb ever drafted and joins it to the table that contains every qb that played in the superbowl. 
--Then Groups by the QB and returns the number of superbowls they have played in as well as their college they attended. 




SELECT MAX(a.Season) as SuperbowlsPlayed, max(b.College) as College, b.Name as Name FROM [70-461].[dbo].[superbowl] as a
LEFT JOIN [70-461].[dbo].[DRAFT] as b
ON a.WinningQb = b.Name OR  a.LosingQB = b.Name
GROUP BY b.Name
ORDER BY 1 DESC
;


--Selects the colleges that have had qbs make it to the superbowl and the amount of times a qb has gone to the superbowl from their school
SELECT COUNT(a.Season) as SuperbowlsPlayed, b.College as College FROM [70-461].[dbo].[superbowl] as a
INNER JOIN [70-461].[dbo].[DRAFT] as b
ON a.WinningQb = b.Name OR  a.LosingQB = b.Name
GROUP BY b.College
ORDER BY 1 DESC
;

Creates a view off of the previous query
CREATE VIEW colleges_with_superbowl_qb
as

SELECT COUNT(a.Season) as SuperbowlsPlayed, b.College as College FROM [70-461].[dbo].[superbowl] as a
INNER JOIN [70-461].[dbo].[DRAFT] as b
ON a.WinningQb = b.Name OR  a.LosingQB = b.Name
GROUP BY b.College
;


--Selects the view we just created, to find the colleges who have produced a superbowl qb
SELECT * FROM colleges_with_superbowl_qb
ORDER BY 1 DESC;

--Selects the highest paid qb coaches
SELECT * FROM [70-461].[dbo].[Highest_paid_qb_coaches]



--Finds the only college qb coaches that are the highest paid, that produced an nfl superbowl qb. This is only 7 out of 22 of the highest paid college qb coaches!
SELECT * FROM [70-461].[dbo].[Highest_paid_qb_coaches] as a
LEFT JOIN colleges_with_superbowl_qb as b
ON a.School = b.College
WHERE SuperbowlsPlayed IS NOT NULL
ORDER BY 3 DESC
;


-- This subquery finds all colleges that did not have a highest paid qb coach that did produce an nfl quarterback.
SELECT a.College FROM colleges_with_superbowl_qb as a
WHERE a.College NOT IN

(SELECT a.School FROM [70-461].[dbo].[Highest_paid_qb_coaches] as a
LEFT JOIN colleges_with_superbowl_qb as b
ON a.School = b.College
--WHERE SuperbowlsPlayed IS NOT NULL
--ORDER BY 3 DESC
)


--Selects the highest paid qb coaches
SELECT * FROM [70-461].[dbo].[Highest_paid_qb_coaches]